USE [ACVSUJournal_00010000]

/********************************************************

  COUNT OF EVENTS PER SECOND IN THE SPECIFIED TIMEFRAME

*********************************************************/
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
	DROP TABLE #Temp

SELECT  CAST(ServerUTC as DATE) as Event_Date, 
		DATEPART(HOUR,ServerUTC) as Hrs, 
		DATEPART(MINUTE, ServerUTC) as Mins,
		DATEPART(SECOND, ServerUTC) as Secs, 
		COUNT(Timestamp) as Total_Entries 
INTO #Temp
FROM [ACVSUJournalLog]
WHERE ServerUTC BETWEEN '2022-04-01 00:00:00' AND '2022-05-09 00:00:00'
GROUP BY CAST(ServerUTC AS DATE), 
		DATEPART(HOUR,ServerUTC), 
		DATEPART(MINUTE, ServerUTC),
		DATEPART(SECOND, ServerUTC)


SELECT CAST(CAST(Event_Date AS VARCHAR(20)) + ' ' + CAST(Hrs AS CHAR(2)) + ':' + CAST(Mins AS CHAR(2)) + ':' + CAST(Secs AS CHAR(2)) AS DATETIME) AS Event_Date_Time,  
	Total_Entries
FROM #Temp
ORDER by 1


/********************************************************

  USING THE SAME DATA SET, GET:
  -MAX AND MIN ENTRIES PER HOUR, 
  -MAX AND MIN ENTRIES PER MINUTE
  -MAX AND MIN ENTRIES PER SECOND 
  FOR EACH DAY IN THE SPECIFIED TIMEFRAME

*********************************************************/

IF OBJECT_ID('tempdb..#TotalsPerHour') IS NOT NULL
	DROP TABLE #TotalsPerHour
IF OBJECT_ID('tempdb..#TotalsPerMinute') IS NOT NULL
	DROP TABLE #TotalsPerMinute
IF OBJECT_ID('tempdb..#TotalsPerSecond') IS NOT NULL
	DROP TABLE #TotalsPerSecond

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Hour, MIN(A.TotalEntries) AS Min_Per_Hour
INTO #TotalsPerHour
FROM
	(SELECT Event_Date, Hrs, SUM(Total_Entries) AS TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs) AS A
GROUP BY Event_Date

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Minute, MIN(A.TotalEntries) AS Min_Per_Minute
INTO #TotalsPerMinute
FROM
	(SELECT Event_Date, Hrs, Mins, SUM(Total_Entries) AS TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs, Mins) AS A
GROUP BY Event_Date

SELECT Event_Date, MAX(A.TotalEntries) AS Max_Per_Second, MIN(A.TotalEntries) AS Min_Per_Second
INTO #TotalsPerSecond
FROM
	(SELECT Event_Date, Hrs, Mins, Secs, SUM(Total_Entries) as TotalEntries
	 FROM #Temp
	 GROUP BY Event_Date, Hrs, Mins, Secs) as A
GROUP BY Event_Date


SELECT DISTINCT t.Event_Date, th.Max_Per_Hour, th.Min_Per_Hour, tm.Max_Per_Minute, tm.Min_Per_Minute, ts.Max_Per_Second, ts.Min_Per_Second
FROM #Temp t
LEFT OUTER JOIN #TotalsPerHour th on t.Event_Date = th.Event_Date
LEFT OUTER JOIN #TotalsPerMinute tm on t.Event_Date = tm.Event_Date
LEFT OUTER JOIN #TotalsPerSecond ts on t.Event_Date = ts.Event_Date
ORDER BY t.Event_Date

DROP TABLE #Temp, #TotalsPerHour, #TotalsPerMinute, #TotalsPerSecond


/********************************************************

  COUNT OF ALL ACTIVE BADGES IN THE SYSTEM

*********************************************************/

USE ACVSCore

SELECT 
	(SELECT COUNT(*) FROM [Access].[Personnel] WHERE ClassType != 'SoftwareHouse.NextGen.Common.SecurityObjects.HiddenPersonnel') as Total_Employee_Records,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' THEN 1 ELSE 0 END) AS Total_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'CONTRACTOR' THEN 1 ELSE 0 END) AS Total_Contractor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' THEN 1 ELSE 0 END) AS Total_Visitor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'NONE' THEN 1 ELSE 0 END) AS Total_None_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'EMPLOYEE' AND UPPER(b.Disabled) = 0 THEN 1 ELSE 0 END) AS Active_Employee_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'CONTRACTOR' AND UPPER(b.Disabled) = 0  THEN 1 ELSE 0 END) AS Active_Contractor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'VISITOR' AND UPPER(b.Disabled) = 0  THEN 1 ELSE 0 END) AS Active_Visitor_Badges,
	SUM(CASE WHEN UPPER(bt.Name) = 'NONE' AND UPPER(b.Disabled) = 0  THEN 1 ELSE 0 END) AS Active_None_Badges

FROM [Access].[Personnel] e
INNER JOIN [Access].Credential b ON e.ObjectID = b.PersonnelId
INNER JOIN [Access].[PersonnelType] bt ON e.[PersonnelTypeID] = bt.ObjectID


/********************************************************

  COUNT OF PANELS

*********************************************************/
SELECT COUNT(*) AS Panel_Count
FROM [Access].[APCController]


/********************************************************

  COUNT OF READERS

*********************************************************/
SELECT COUNT(*) as Total_Readers
FROM [Access].[Reader]

/********************************************************

  COUNT OF OUTPUTS

*********************************************************/
SELECT COUNT(*) AS Output_Pin_Count
FROM [Access].[Output]

/********************************************************

  COUNT OF INPUTS

*********************************************************/
SELECT COUNT(*) AS Input_Pin_Count
FROM [Access].[Input]